//
//  SQLiteDataBaseManager.m
//  18-SQLite
//
//  Created by dllo on 16/8/8.
//  Copyright © 2016年 ZHW. All rights reserved.
//

#import "SQLiteDataBaseManager.h"
#import "Student.h"
@implementation SQLiteDataBaseManager
// 单例创建方式 只有一个对象
// 内存管理中 单例不能被释放
+ (SQLiteDataBaseManager *)shareManager {
    static SQLiteDataBaseManager *manager = nil;
    // 保证线程安全的情况下 只执行一次
    static dispatch_once_t onceToken;
    dispatch_once(&onceToken, ^{
        manager = [[SQLiteDataBaseManager alloc] init];
    });
    return manager;
}

- (BOOL)openSQLite3 {
    NSArray *pathArray =
    NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *document = [pathArray firstObject];
    // 数据库SQLite文件类型 .db
    NSString *dbPath = [document stringByAppendingPathComponent:@"student.db"];
    // UTF8String属性 将OC字符串转化成C语言字符串
    NSLog(@"%@", dbPath);
    BOOL isOpen = YES;
    // 参数1: 数据库文件路径 参数2: 指向指针的指针, 该指针可以被修改
    int result = sqlite3_open([dbPath UTF8String], &dbPoint);
    // sqlite3_open 当路径文件不存在时, 文件会被SQLite创建并打开 / 存在时直接打开
    // 返回值为int 0(SQLITE_OK)代表成功; 1代表失败
    if (result == SQLITE_OK) {
        NSLog(@"打开数据库成功");
        isOpen = YES;
    } else {
        NSLog(@"打开失败错误为%d", result);
        isOpen = NO;
    }
    return isOpen;
}

- (BOOL)closeSQLite3 {
  
    int result = sqlite3_close(dbPoint);
    return [self isOperationResult:result alertText:@"关闭"];
}

- (BOOL)creatTable {
    // 创建表
    // CREATE TABLE 表名 (字段1 类型 约束, 字段2, 字段3)
    // PRIMARY KEY 一个表 有且只有一个主键
    // AUTOINCREMENT 自增 添加完该字段 系统会自己创建一个数据库 sqlite_sequence 用来保存自增的值
    // NOT NULL 不能为空 为空会报SQL错
    NSString *creatSQL = @"CREATE TABLE stu1 (stu_id INTEGER PRIMARY KEY AUTOINCREMENT, stu_name TEXT NOT NULL)";
    int result = sqlite3_exec(dbPoint, [creatSQL UTF8String], NULL, NULL, NULL);
    return [self isOperationResult:result alertText:@"创建表"];
    // 删除表
    // drop table 表名
}

- (BOOL)insertStu:(Student *)stu {
    NSString *insertSQL = [NSString stringWithFormat:@"INSERT INTO stu1 (stu_id, stu_name) VALUES (%ld, '%@');", stu.stu_id, stu.stu_name];
    // 自增字段 autoincrement
    // 默认如果填入null 值会从1开始
    // 如果填入其他值 会从其他值开始 例:填入110 从110开始计算
    int result = sqlite3_exec(dbPoint, [insertSQL UTF8String], NULL, NULL, NULL);
    return [self isOperationResult:result alertText:@"插入数据"];
}

- (BOOL)deleteStu:(Student *)stu {
    NSString *deleteSQL = [NSString stringWithFormat:@"DELETE FROM stu1 WHERE stu_id=%ld", stu.stu_id];
    int result = sqlite3_exec(dbPoint, [deleteSQL UTF8String], NULL, NULL, NULL);
    return [self isOperationResult:result alertText:@"删除"];
}

- (BOOL)deleteAll {
    NSString *deleteAllSQL = @"DELETE FROM stu1 WHERE 1=1";
    int result = sqlite3_exec(dbPoint, [deleteAllSQL UTF8String], NULL, NULL, NULL);
    return [self isOperationResult:result alertText:@"删除全部"];
}

- (BOOL)updateOldName:(NSString *)oldName newName:(NSString *)newName {
    // UPDATE 表名 set 字段名 = 新值 WHERE 字段名 = 旧值
    NSString *updateSQL = [NSString stringWithFormat:@"UPDATE stu1 SET stu_name = '%@' WHERE stu_name = '%@'", newName, oldName];
    int result = sqlite3_exec(dbPoint, [updateSQL UTF8String], NULL, NULL, NULL);
    return [self isOperationResult:result alertText:@"更新数据"];
}

- (__kindof NSArray *)selectAll {
    // select 字段名
    NSString *selectAll = @"SELECT * FROM stu1";
    sqlite3_stmt *stmt = NULL;
    // 参数1: 数据库指针
    // 参数2: SQL语句
    // 参数3: SQL语句的长度
    // 参数4: 替身 查询出的内容放入替身中 并将替身返回
    int result = sqlite3_prepare(dbPoint, [selectAll UTF8String], -1, &stmt, NULL);
    NSMutableArray *array = [NSMutableArray array];
    if (result == SQLITE_OK) {
        // 循环 遍历替身内容
        // step查询替身中有没有其他数据
        // 当包含其他数据时 结果为SQLITE_ROW
        // 当不包含其他数据时, 结果为SQLITE_DONE
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            // 取出第0列的数据 根据类型选择函数
            int stu_id = sqlite3_column_int(stmt, 0);
            // 取出第1列的数据, 根据类型选择函数
            const unsigned char *stu_name = sqlite3_column_text(stmt, 1);
            Student *stu = [[Student alloc] init];
            stu.stu_id = stu_id;
            stu.stu_name = [NSString stringWithUTF8String:(const char *)stu_name];
            [array addObject:stu];
        }
    }
    // 循环结束后 销毁替身
    sqlite3_finalize(stmt);
    return array;
}

- (BOOL)isOperationResult:(int)result alertText:(NSString *)text {
    BOOL isOperation = YES;
    if (result == SQLITE_OK) {
        NSLog(@"%@成功", text);
        isOperation = YES;
    } else {
        NSLog(@"%@失败错误为%d", text, result);
        isOperation = NO;
    }
    return isOperation;
}

@end
